﻿using System;
using System.Data;
using System.Collections;
using System.Text;
using System.Data.SqlClient;
using OgilvyOne.IDAL;
using OgilvyOne.SqlServerDAL;
using OgilvyOne.Utility;
using OgilvyOne.Model;
namespace OgilvyOne.SqlServerDAL
{
    /// <summary>
    /// 数据访问类:TemplateLabel
    /// </summary>
    public class TemplateLabel : ITemplateLabel
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        public TemplateLabel()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int LABEL_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from CMS_TEMPLATE_LABEL");
            strSql.Append(" where LABEL_ID=@LABEL_ID");
            SqlParameter[] parameters = {
             new SqlParameter("@LABEL_ID", SqlDbType.Int,4)
};
            parameters[0].Value = LABEL_ID;

            return int.Parse(SQLHelper.ExecuteScalar(connstr, CommandType.Text, strSql.ToString(), parameters).ToString()) > 0;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="LABEL_ID"></param>
        /// <returns></returns>
        public bool Exists(string name, int LABEL_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from CMS_TEMPLATE_LABEL");
            strSql.Append(" where LABEL_NAME=@LABEL_NAME and LABEL_ID not in (" + LABEL_ID + ")");
            SqlParameter[] parameters = {
             new SqlParameter("@LABEL_NAME", SqlDbType.VarChar,50)
};
            parameters[0].Value = name;

            return int.Parse(SQLHelper.ExecuteScalar(connstr, CommandType.Text, strSql.ToString(), parameters).ToString()) > 0;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(OgilvyOne.Model.TemplateLabelModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CMS_TEMPLATE_LABEL(");
            strSql.Append("LABEL_NAME,LABEL_DEF,LABEL_VALUE,ADDTIME,IS_HTML,HTML_FILE_NAME,LABEL_TYPE,MODEL_TYPE)");
            strSql.Append(" values (");
            strSql.Append("@LABEL_NAME,@LABEL_DEF,@LABEL_VALUE,@ADDTIME,@IS_HTML,@HTML_FILE_NAME,@LABEL_TYPE,@MODEL_TYPE)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
new SqlParameter("@LABEL_NAME", SqlDbType.VarChar,50),
new SqlParameter("@LABEL_DEF", SqlDbType.VarChar,50),
new SqlParameter("@LABEL_VALUE", SqlDbType.VarChar,8000),
new SqlParameter("@ADDTIME", SqlDbType.DateTime),
new SqlParameter("@IS_HTML", SqlDbType.Int),
new SqlParameter("@HTML_FILE_NAME", SqlDbType.VarChar,50),
                                           new SqlParameter("@LABEL_TYPE", SqlDbType.VarChar, 50),
                                        new SqlParameter("@MODEL_TYPE", SqlDbType.Int, 4)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Def;
            parameters[2].Value = model.Value;
            parameters[3].Value = model.Addtime;
            parameters[4].Value = model.Ishtml;
            
            if (model.HtmlFileName == null)
            {
                parameters[5].Value = "";
            }
            else
            {
                parameters[5].Value = model.HtmlFileName;
            }
            parameters[6].Value = model.LabelType;
            parameters[7].Value = model.ModelType;
            CmsConstants.isLabelUpdate = true;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(OgilvyOne.Model.TemplateLabelModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CMS_TEMPLATE_LABEL set ");
            strSql.Append("LABEL_NAME=@LABEL_NAME,");
            strSql.Append("LABEL_DEF=@LABEL_DEF,");
            strSql.Append("LABEL_VALUE=@LABEL_VALUE,");
            strSql.Append("IS_HTML=@IS_HTML,");
            strSql.Append("HTML_FILE_NAME=@HTML_FILE_NAME,");
            strSql.Append("LABEL_TYPE=@LABEL_TYPE,MODEL_TYPE=@MODEL_TYPE");
            strSql.Append(" where LABEL_ID=@LABEL_ID");
            SqlParameter[] parameters = {
new SqlParameter("@LABEL_NAME", SqlDbType.VarChar,50),
new SqlParameter("@LABEL_DEF", SqlDbType.VarChar,50),
new SqlParameter("@LABEL_VALUE", SqlDbType.VarChar,8000),
new SqlParameter("@IS_HTML", SqlDbType.Int),
new SqlParameter("@HTML_FILE_NAME", SqlDbType.VarChar,50),
new SqlParameter("@LABEL_TYPE", SqlDbType.VarChar, 50),
new SqlParameter("@LABEL_ID", SqlDbType.Int,4),
                                         new SqlParameter("@MODEL_TYPE", SqlDbType.Int, 4)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Def;
            parameters[2].Value = model.Value;
            parameters[3].Value = model.Ishtml;
            parameters[4].Value = model.HtmlFileName;
            parameters[5].Value = model.LabelType;
            parameters[6].Value = model.Id;
            parameters[7].Value = model.ModelType;
            CmsConstants.isLabelUpdate = true;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_TEMPLATE_LABEL ");
            strSql.Append(" where LABEL_ID=@LABEL_ID");
            SqlParameter[] parameters = {
new SqlParameter("@LABEL_ID", SqlDbType.Int,4)
};
            parameters[0].Value = id;
            CmsConstants.isLabelUpdate = true;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string LABEL_IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_TEMPLATE_LABEL ");
            strSql.Append(" where LABEL_ID in (" + LABEL_IDlist + ")  ");
            CmsConstants.isLabelUpdate = true;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public OgilvyOne.Model.TemplateLabelModel GetModel(int LABEL_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 LABEL_ID,LABEL_NAME,LABEL_DEF,LABEL_VALUE,ADDTIME,IS_HTML,HTML_FILE_NAME,LABEL_TYPE,MODEL_TYPE from CMS_TEMPLATE_LABEL ");
            strSql.Append(" where LABEL_ID=@LABEL_ID");
            SqlParameter[] parameters = {
new SqlParameter("@LABEL_ID", SqlDbType.Int,4)
};
            parameters[0].Value = LABEL_ID;

            OgilvyOne.Model.TemplateLabelModel model = new OgilvyOne.Model.TemplateLabelModel();
            using (SqlDataReader dr = SQLHelper.ExecuteReader(connstr, CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        model.Id = dr.GetInt32(0);
                    }
                    if (!dr.IsDBNull(1))
                    {
                        model.Name = dr.GetString(1);
                    }
                    if (!dr.IsDBNull(2))
                    {
                        model.Def = dr.GetString(2);
                    }
                    if (!dr.IsDBNull(3))
                    {
                        model.Value = dr.GetString(3);
                    }
                    if (!dr.IsDBNull(4))
                    {
                        model.Addtime = dr.GetDateTime(4);
                    }
                    if (!dr.IsDBNull(5))
                    {
                        model.Ishtml = dr.GetInt32(5);
                    }
                    if (!dr.IsDBNull(6))
                    {
                        model.HtmlFileName = dr.GetString(6);
                    }
                    if (!dr.IsDBNull(7))
                    {
                        model.LabelType = dr.GetString(7);
                    }
                    if (!dr.IsDBNull(8))
                    {
                        model.ModelType = dr.GetInt32(8);
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select LABEL_ID,LABEL_NAME,LABEL_DEF,LABEL_VALUE,ADDTIME,IS_HTML,HTML_FILE_NAME,LABEL_TYPE,MODEL_TYPE ");
            strSql.Append(" FROM CMS_TEMPLATE_LABEL ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by LABEL_ID");
            return SQLHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" LABEL_ID,LABEL_NAME,LABEL_DEF,LABEL_VALUE,ADDTIME,IS_HTML,HTML_FILE_NAME,LABEL_TYPE ");
            strSql.Append(" FROM CMS_TEMPLATE_LABEL ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得标签TABLE
        /// </summary>
        public Hashtable GetLabelHashtable()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select LABEL_DEF,LABEL_VALUE,LABEL_TYPE,IS_HTML,HTML_FILE_NAME,MODEL_TYPE ");
            strSql.Append(" FROM CMS_TEMPLATE_LABEL ");
            Hashtable map = new Hashtable();
            SqlParameter[] parameters = {

            };

            using (SqlDataReader dr = SQLHelper.ExecuteReader(connstr, CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    string key = "";
                    if (!dr.IsDBNull(0))
                    {
                        key = dr.GetString(0);
                    }
                    TemplateLabelModel tlm = new TemplateLabelModel();
                    string value = "";
                    if (!dr.IsDBNull(1))
                    {
                        value = dr.GetString(1);
                    }
                    tlm.Value = value;
                    string type = "";
                    if (!dr.IsDBNull(2))
                    {
                        type = dr.GetString(2);
                    }
                    tlm.LabelType = type;

                    if (!dr.IsDBNull(3)) tlm.Ishtml = dr.GetInt32(3);
                    if (!dr.IsDBNull(4)) tlm.HtmlFileName = dr.GetString(4);
                    if (!dr.IsDBNull(5)) tlm.ModelType = dr.GetInt32(5);

                    if (key != "")
                    {
                        map.Add(key, tlm);
                    }
                }
            }
            CmsConstants.isLabelUpdate = false;
            return map;

        }


        #endregion  Method
    }
}

